﻿///***************************************************************
/// 시  스  템 : Updms.Crd.Dac
/// 단위시스템 : CRD
/// 계      층 : Data Access Layer
/// 프로그램명 : Crd_Dac_UPDMS_CRD_REPORT_Crd3013p
/// 파  일  명 : Crd_Dac_UPDMS_CRD_REPORT_Crd3013p.cs
/// 설      명 : 월간사용분석표(연간사용분석표에서 X월 클릭시 팝업)
///***************************************************************
/// 수정ID 일자       이름     내용
///---------------------------------------------------------------
/// CH001  2010-07-19 송시명   최초 작성
///***************************************************************
using System;
using System.Data;
using System.Data.SqlClient;
using System.EnterpriseServices;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;

using SMFramework.Data;

using Base.Dac;

namespace Updms.Crd.Dac
{
	[ComVisible(true)]
	[Transaction(TransactionOption.Supported)]
	[JustInTimeActivation(true)]
	public class Crd_Dac_UPDMS_CRD_REPORT_Crd3013p : Base.AppFramework.DacBase
	{
		Global global = new Global();

		public Crd_Dac_UPDMS_CRD_REPORT_Crd3013p()
		{
		}

		#region NT [GetMonthlyAmountByCardNAccount]

		[AutoComplete]
		public DataSet GetMonthlyAmountByCardNAccount(DataPack lo_dp)
		{
			string ls_curr_method
				= MethodBase.GetCurrentMethod().DeclaringType.ToString() + " - " + MethodBase.GetCurrentMethod().ToString();
			
			StringBuilder sb = new StringBuilder();
			
			string ls_card_cd = lo_dp["ls_card_cd"].ToString();
			string ls_card_nm = lo_dp["ls_card_nm"].ToString();
			string ls_base_ym = lo_dp["ls_base_ym"].ToString();
			string ls_lang_set = lo_dp["ls_lang_set"].ToString();
			bool lb_is_excel = Convert.ToBoolean(lo_dp["ls_is_excel"].ToString());

			//CC02X,CC06X,CC07X,CC08X
			string[] ls_card_cd_list = ls_card_cd.Split(',');
			string[] ls_card_mn_list = ls_card_nm.Split(',');

			sb.Append("SELECT View_Order,\n");

			if (lb_is_excel)
			{
				sb.Append("       Account_Nm,\n");
			}
			else
			{
				sb.Append("       Account_Nm AS '" + global.GetLabelNmBySeq(19, ls_lang_set) + "',\n");
			}
			
			for (int i=0; i<ls_card_cd_list.Length; i++)
			{
				if (lb_is_excel)
				{
					sb.Append("       ISNULL(" + ls_card_cd_list[i] + ", 0) AS '" + ls_card_mn_list[i] + "',\n");
				}
				else
				{
					sb.Append("       dbo.ufn_UPDMS_Convert_Money(ISNULL(" + ls_card_cd_list[i] + ", 0)) AS '" + ls_card_mn_list[i] + "',\n");
				}
			}

			string ls_total_amt = "";
			for (int i = 0; i < ls_card_cd_list.Length; i++)
			{
				ls_total_amt += "ISNULL(" + ls_card_cd_list[i] + ", 0)+";
			}			

			if (lb_is_excel)
			{
				ls_total_amt = ls_total_amt.Remove(ls_total_amt.Length - 1, 1) + " AS 'Total'";
			}
			else
			{
				ls_total_amt = "dbo.ufn_UPDMS_Convert_Money(" + ls_total_amt.Remove(ls_total_amt.Length - 1, 1) + ") AS 'Total'";
			}

			sb.Append(ls_total_amt + "\n");
			
			sb.Append(" FROM\n");
			sb.Append("    (\n");
			sb.Append("   SELECT t.Card_Cd,\n");
			sb.Append("          t.Account AS View_Order,\n");
			sb.Append("          'M'+RIGHT(t.Paymnt_Dt, 2) AS Paymnt_Dt,\n");
			sb.Append("          t.Account,\n");
			sb.Append("          t.Account_Nm,\n");
			sb.Append("          t.Amount\n");
			sb.Append("     FROM\n");
			sb.Append("		   (\n");
			sb.Append("         SELECT crd.Card_Cd,\n");
			sb.Append("                crd.Paymnt_Dt,\n");
			sb.Append("                crd.Account,\n");
			sb.Append("                MAX(crd.Account_Nm) AS Account_Nm,\n");
			sb.Append("                SUM(ISNULL(crd.Amount,0)) AS Amount\n");
			sb.Append("           FROM (/*일시불*/\n");
			sb.Append("                 SELECT Card_Cd,\n");
			sb.Append("                        LEFT(c.Paymnt_Dt, 7) AS Paymnt_Dt,\n");
			sb.Append("                        c.Account,\n");
			sb.Append("                        dbo.ufn_UPDMS_Get_Code_Name(c.Account, 'CRD', '" + ls_lang_set + "') AS Account_Nm,\n");
			sb.Append("                        SUM(ISNULL(c.Amount, 0)) AS Amount\n");
			sb.Append("                   FROM UPDMS_CRD_ITEM_BIZ c\n");
			sb.Append("                  WHERE c.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma('" + ls_card_cd + "'))\n");
			sb.Append("                    AND LEFT(c.Paymnt_Dt, 7) = '" + ls_base_ym + "'\n");
			sb.Append("                  GROUP BY Card_Cd, LEFT(c.Paymnt_Dt, 7), c.Account\n");
			sb.Append("                 UNION ALL\n");
			sb.Append("                 /*할부*/\n");
			sb.Append("                 SELECT ucir.Card_Cd,\n");
			sb.Append("                        LEFT(ucir.Paymnt_Dt, 7) AS Paymnt_Dt,\n");
			sb.Append("                        ucib.Account,\n");
			sb.Append("                        dbo.ufn_UPDMS_Get_Code_Name(ucib.Account, 'CRD', '" + ls_lang_set + "'),	\n");
			sb.Append("                        SUM(ISNULL(ucir.Ins_Month_Amt,0))\n");
			sb.Append("                   FROM UPDMS_CRD_INSTALLMENT_REPAY AS ucir\n");
			sb.Append("                   JOIN UPDMS_CRD_INSTALLMENT_BIZ AS ucib\n");
			sb.Append("                     ON ucir.Ins_Cd = ucib.Ins_Cd\n");
			sb.Append("                  WHERE ucir.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma('" + ls_card_cd + "'))\n");
			sb.Append("                    AND LEFT(ucir.Paymnt_Dt, 7) = '" + ls_base_ym + "'\n");
			sb.Append("					 GROUP BY ucir.Card_Cd, LEFT(ucir.Paymnt_Dt, 7), ucib.Account\n");
			sb.Append("					) crd\n");
			sb.Append("			 GROUP BY crd.Card_Cd, crd.Paymnt_Dt, crd.Account\n");
			sb.Append("         UNION ALL\n");
			sb.Append("         /*총계*/\n");
			sb.Append("         SELECT crd.Card_Cd,\n");
			sb.Append("	               crd.Paymnt_Dt,\n");
			sb.Append("                'BB006' AS Account,\n");
			sb.Append("                dbo.ufn_UPDMS_Get_Code_Name('BB006', 'CRD', '" + ls_lang_set + "') AS Account_Nm,\n");
			sb.Append("                SUM(ISNULL(crd.Amount,0)) AS Amount\n");
			sb.Append("           FROM (/*일시불*/\n");
			sb.Append("					SELECT Card_Cd,\n");
			sb.Append("					       LEFT(c.Paymnt_Dt, 7) AS Paymnt_Dt,\n");
			sb.Append("						   SUM(ISNULL(c.Amount, 0)) AS Amount\n");
			sb.Append("					  FROM UPDMS_CRD_ITEM_BIZ c\n");
			sb.Append("					 WHERE c.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma('" + ls_card_cd + "'))\n");
			sb.Append("					   AND LEFT(c.Paymnt_Dt, 7) = '" + ls_base_ym + "'\n");
			sb.Append("					 GROUP BY Card_Cd, LEFT(c.Paymnt_Dt, 7)\n");
			sb.Append("					UNION ALL\n");
			sb.Append("					/*할부*/\n");
			sb.Append("					SELECT ucir.Card_Cd,\n");
			sb.Append("					       LEFT(ucir.Paymnt_Dt, 7) AS Paymnt_Dt,\n");
			sb.Append("						   SUM(ISNULL(ucir.Ins_Month_Amt,0)) AS Amount\n");
			sb.Append("					  FROM UPDMS_CRD_INSTALLMENT_REPAY AS ucir\n");
			sb.Append("					  JOIN UPDMS_CRD_INSTALLMENT_BIZ AS ucib\n");
			sb.Append("						ON ucir.Ins_Cd = ucib.Ins_Cd\n");
			sb.Append("					 WHERE ucir.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma('" + ls_card_cd + "'))\n");
			sb.Append("					   AND LEFT(ucir.Paymnt_Dt, 7) = '" + ls_base_ym + "'\n");
			sb.Append("					 GROUP BY ucir.Card_Cd, LEFT(ucir.Paymnt_Dt, 7)\n");
			sb.Append("					) crd\n");
			sb.Append("			 GROUP BY crd.Card_Cd, crd.Paymnt_Dt\n");
			sb.Append("			 ) t\n");
			sb.Append("	 ) pv\n");
			sb.Append(" PIVOT ( SUM(pv.Amount) FOR pv.Card_Cd IN (" + ls_card_cd + ") ) AS pvt\n");
			
			DataSet ds = new DataSet();

			return _agent.Fill(sb.ToString(), "UPDMS_CRD_REPORT", ds, null, CommandType.Text, ls_curr_method);
		}

		#endregion

		#region NT [GetIncDecAmountByAccountFromPreMonth]
		
		/// <summary>
		/// 계정별 전월대비 증감액
		/// </summary>
		/// <param name="lo_dp"></param>
		/// <returns></returns>
		[AutoComplete]
		public DataSet GetIncDecAmountByAccountFromPreMonth(DataPack lo_dp)
		{
			string ls_sql = "usp_UPDMS_CRD_REPORT_Crd3013p_Get_Inc_Dec_Amount_By_Account_From_Pre_Month";

			SqlParameter[] paramArray = lo_dp.ToSqlParameters();

			DataSet ds = new DataSet();
			return _agent.Fill(ls_sql, "UPDMS_CRD_REPORT", ds, paramArray, CommandType.StoredProcedure);
		}

		#endregion

	}
}
